
DECLARE @MONTH1 DATE=DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,(SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT))-10,0))
--PRINT @MONTH1
DECLARE @COUNT INT =1
DECLARE @FULLDATE NVARCHAR(8) = convert(nvarchar(8),@month1,112)
DELETE FROM AVGBAL_LAST_12_MONTH
WHILE @COUNT <13
BEGIN
	DECLARE @MONTH NVARCHAR(6) = CONVERT(NVARCHAR(6), @MONTH1,112)
	
	EXEC('
IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''AVGBAL_RAW'+@MONTH+''')
DROP TABLE AVGBAL_RAW'+@MONTH+'

SELECT A.CIF,B.DAO,A.AVGBAL_QD AS AVGBAL
INTO AVGBAL_RAW'+@MONTH+'
FROM GIANGLH3.DAILY.DBO.AVGBAL'+@MONTH+' A, TBL_CUSTOMER B
WHERE A.APP=''LOAN'' AND A.CIF COLLATE DATABASE_DEFAULT = B.CIF
')

PRINT(@MONTH)
EXEC('
INSERT AVGBAL_LAST_12_MONTH
SELECT ''MONTH'+@COUNT+''' AS MONTH,CIF,DAO,SUM(AVGBAL) AS AVGBAL
FROM AVGBAL_RAW'+@MONTH+' WHERE CIF IS NOT NULL AND DAO IS NOT NULL
GROUP BY CIF, DAO ')


	--LAST DAY OF ANY MONTH AND YEAR
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+2,0))
--PRINT @MONTH1
SET @COUNT = @COUNT + 1
END


--SELECT * FROM [ANHCP].[CUONGDB].[DBO].[AVGBAL201611]

--DELETE FROM AVGBAL_LAST_12_MONTH

delete from TBL_AVGBAL_ACCUMULATED

insert TBL_AVGBAL_ACCUMULATED 
select (SELECT MAX_DATE_ENDMONTH FROM DATE_EM_REPORT) AS MONTH,*
from 
(SELECT CIF,dao,AVGBAL,month
FROM AVGBAL_LAST_12_MONTH ) AS A
PIVOT
(MAX(AVGBAL) FOR month IN (month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)) AS PVT
where CIF in (select cif from TBL_CUSTOMER)


--select * from TBL_AVGBAL_ACCUMULATED

set @count = 1

  while (@count <13)
  begin 
  exec('
  update TBL_AVGBAL_ACCUMULATED set month'+@count+'=0 where MONTH'+@count+' is null
  ')
  set @COUNT = @COUNT +1
  end

  delete from TBL_PROFITABILITY

	insert TBL_PROFITABILITY
  SELECT B.MONTH, 
		B.CIF, 
		B.DAO_CIF, 
		B.MONTH1/NULLIF(A.MONTH1, 0) AS MONTH1, 
		B.MONTH2/NULLIF(A.MONTH2,  0)AS MONTH2, 
		B.MONTH3/NULLIF(A.MONTH3,  0)AS MONTH3, 
		B.MONTH4/NULLIF(A.MONTH4,  0)AS MONTH4, 
		B.MONTH5/NULLIF(A.MONTH5,  0)AS MONTH5, 
		B.MONTH6/NULLIF(A.MONTH6,  0)AS MONTH6, 
		B.MONTH7/NULLIF(A.MONTH7,  0)AS MONTH7, 
		B.MONTH8/NULLIF(A.MONTH8,  0)AS MONTH8, 
		B.MONTH9/NULLIF(A.MONTH9,  0)AS MONTH9, 
		B.MONTH10/NULLIF(A.MONTH10,  0)AS MONTH10, 
		B.MONTH11/NULLIF(A.MONTH11,  0)AS MONTH11, 
		B.MONTH12/NULLIF(A.MONTH12, 0)AS MONTH12,
		2 AS PROFIT_TYPE
  FROM TBL_AVGBAL_ACCUMULATED A, TBL_TOI_ACCUMULATED B
  WHERE A.CIF = B.CIF AND A.DAO = B.DAO_CIF

  insert TBL_PROFITABILITY 
  select *, 1
  from TBL_TOI_ACCUMULATED


  --select * from TBL_PROFITABILITY where cif = '2040178'


  set @count = 1

  while (@count <13)
  begin 
  exec('
  update TBL_PROFITABILITY set month'+@count+'=0 where MONTH'+@count+' is null
  ')
  set @COUNT = @COUNT +1
  end

  --select * from TBL_PROFITABILITY where cif='926835'

  --select distinct cif from TBL_PROFITABILITY
  --SELECT * FROM TBL_AVGBAL_ACCUMULATED WHERE MONTH1 < 0